package cn.sptpc.dao.impl;

import cn.sptpc.dao.UserDao;
import cn.sptpc.entity.java.User;
import cn.sptpc.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoI implements UserDao {

    private DBUtil dbUtil = DBUtil.getDbUtil();

    /**
     * 通过学号和密码查询一个用户
     *
     * @param id
     * @param password
     * @return
     */
    @Override
    public User getUser(int id, String password) {
        Connection con = dbUtil.getConnection();
        //预处理语句
        String sql = "select * from oa_user where id=? and password = ?";
        try {
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setInt(1,id);
            pstm.setString(2,password);
            ResultSet rs = pstm.executeQuery();
            User user = new User();
            while (rs.next()){
                String username = rs.getString("username");
                Long sex = rs.getLong("sex");
                String classr = rs.getString("classr");
                user.setId(rs.getLong("id"));
                user.setUsername(username);
                user.setSex(sex);
                user.setClassr(classr);
                user.setDepartmentId(rs.getInt("department_id"));
                user.setDepartment(rs.getString("department"));
                user.setGroupId(rs.getInt("group_id"));
                user.setGroup(rs.getString("group"));
                user.setPhone(rs.getString("phone"));
                user.setAvater(rs.getString("avater"));
                user.setPermissions(rs.getInt("permissions"));
            }
            return  user;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 通过id 查询一条记录
     *
     * @param id
     * @return
     */
    @Override
    public User getUserById(Long id) {
        Connection con = dbUtil.getConnection();

        //预处理语句
        String sql = "select * from oa_user where id=? ";
        try {
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setLong(1,id);
            ResultSet rs = pstm.executeQuery();
            User user =null;
            while (rs.next()){
                user = new User();
                user.setId(rs.getLong("id"));
                user.setUsername( rs.getString("username"));
                user.setSex(rs.getLong("sex"));
                user.setClassr(rs.getString("classr"));
                user.setDepartmentId(rs.getInt("department_id"));
                user.setDepartment(rs.getString("department"));
                user.setGroupId(rs.getInt("group_id"));
                user.setGroup(rs.getString("group"));
                user.setPhone(rs.getString("phone"));
                user.setAvater(rs.getString("avater"));
                user.setPermissions(rs.getInt("permissions"));
            }
            return  user;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 分页查询
     *
     * @param pageUtil
     * @return
     */
    @Override
    public List<User> queryAllUser(int[] pageUtil,User iUser) {
        List<User> users = new ArrayList<>();
        Connection con = dbUtil.getConnection();
        PreparedStatement pstm;
        int permissions = (int) iUser.getPermissions();

        String sql="";
        if (permissions == 0){
            sql = "select * from oa_user limit ?,?";
        } else {
            sql = "SELECT * FROM oa_user WHERE department_id = ? limit ?,?";
        }

        try {
            pstm = con.prepareStatement(sql);
            if (permissions == 0){
                pstm.setInt(1,pageUtil[0]);
                pstm.setInt(2,pageUtil[1]);
            } else {
                pstm.setInt(1,(int)iUser.getDepartmentId());
                pstm.setInt(2,pageUtil[0]);
                pstm.setInt(3,pageUtil[1]);
            }
            ResultSet rs = pstm.executeQuery();
            while (rs.next()){
                User user = new User();
                user = new User();
                user.setId(rs.getLong("id"));
                user.setUsername( rs.getString("username"));
                user.setSex(rs.getLong("sex"));
                user.setClassr(rs.getString("classr"));
                user.setDepartmentId(rs.getInt("department_id"));
                user.setDepartment(rs.getString("department"));
                user.setGroupId(rs.getInt("group_id"));
                user.setGroup(rs.getString("group"));
                user.setPhone(rs.getString("phone"));
                user.setAvater(rs.getString("avater"));
                user.setPermissions(rs.getInt("permissions"));
                users.add(user);
            }
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 查询所有行数量
     *
     * @return
     */
    @Override
    public Integer queryCount() {
        Integer i = null;
        Connection con = dbUtil.getConnection();
        String sql = "select count(id) from oa_user";
        try {
            PreparedStatement pstm = con.prepareStatement(sql);
            ResultSet rs = pstm.executeQuery();
            while (rs.next()){
                i = rs.getInt(1);
            }
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 插入一条记录
     *
     * @param user
     * @return
     */
    @Override
    public Integer insetAUser(User user) {
        Connection con = dbUtil.getConnection();
        String sql = "insert into user (id,username,password,sex,department,`group`,phone,permissions)value(?,?,?,?,?,?,?,?)";
        Integer i = null;
        try {
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setLong(1,user.getId());
            pstm.setString(2,user.getUsername());
            pstm.setString(3,user.getPassword());
            pstm.setLong(4,user.getSex());
            pstm.setLong(5,user.getDepartmentId());
            pstm.setLong(6,user.getGroupId());
            pstm.setString(7,user.getPhone());
            pstm.setLong(8,user.getPermissions());
            i = pstm.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 查询某部门用户记录
     *
     * @param departmentId
     * @return
     */
    @Override
    public Integer queryDepartmentUserCount(int departmentId) {
        Connection con = dbUtil.getConnection();
        String sql = "SELECT USER.id,USER.username,USER.sex,USER.class,department.department_name,`group`.`group`,USER.phone,USER.Avatar,USER.permissions,USER.department,USER.group FROM USER,department,`group` WHERE USER.department = department.id AND USER.`group` = `group`.id AND USER.`department`=?";
        Integer i = null;
        try {
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setLong(1,departmentId);
            ResultSet rs = pstm.executeQuery();

            while (rs.next()){
                i = rs.getInt(1);
            }
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 通过id删除一条记录
     *
     * @param id
     * @return
     */
    @Override
    public Integer deleteUser(int id){
        Connection con = dbUtil.getConnection();
        String sql = "delete from user where id = ?";
        int i;
        try {
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setInt(1,id);
            i = pstm.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 通过id 更新一条记录
     *
     * @param bean
     * @return
     */
    @Override
    public Integer updataUser(User bean) {
        Connection con = dbUtil.getConnection();
        String sql = "update user set sex = ?,class = ?,department = ?,`group` = ? ,phone = ?,Avatar = ?,permissions = ? where id = ?";
        int i = 0;
        try {
            PreparedStatement pstm = con.prepareStatement(sql);
            pstm.setLong(1,bean.getSex());
            pstm.setString(2,bean.getClassr());
            pstm.setLong(3,bean.getDepartmentId());
            pstm.setLong(4,bean.getGroupId());
            pstm.setString(5,bean.getPhone());
            pstm.setString( 6,bean.getAvater());
            pstm.setLong(7,bean.getPermissions());
            pstm.setLong(8,bean.getId());
            i = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
    @Override
    public Integer updataAUser(User bean) {
        Integer i = null;
        Connection con = dbUtil.getConnection();
        String sql = "";
        if (bean.getPassword() == null){
            sql = "update user set username = ?,department = ?,`group` = ? ,phone = ? where id = ?";
        }else {
            sql = "update user set username = ?,department = ?,`group` = ? ,phone = ?,password = ? where id = ?";
        }

        try {
            PreparedStatement pstm = con.prepareStatement(sql);
            if (bean.getPassword() == null){
                pstm.setString(1,bean.getUsername());
                pstm.setLong(2,bean.getDepartmentId());
                pstm.setLong(3,bean.getGroupId());
                pstm.setString(4,bean.getPhone());
                pstm.setLong(5,bean.getId());

            }else {
                pstm.setString(1,bean.getUsername());
                pstm.setLong(2,bean.getDepartmentId());
                pstm.setLong(3,bean.getGroupId());
                pstm.setString(4,bean.getPhone());
                pstm.setString(5,bean.getPassword());
                pstm.setLong(6,bean.getId());
            }
            i = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    /**
     * 查询用户
     *
     * @param key
     * @return
     */
    @Override
    public List<User> queryUser(String key,User iUser) {
        List<User> users = new ArrayList<>();
        Connection con = dbUtil.getConnection();
        int permissions = (int) iUser.getPermissions();
        String sql = "";
        if (permissions == 0){
            sql = "SELECT * FROM oa_user WHERE id = ? OR username = ?";
        }else {
            sql = "SELECT * FROM oa_user WHERE (id = ? OR username = ?) And department_id = ?";
        }
        try {
            PreparedStatement pstm = con.prepareStatement(sql);
            if (permissions == 0){
                pstm.setString(1,key);
                pstm.setString(2,key);
            }else {
                pstm.setString(1,key);
                pstm.setString(2,key);
                pstm.setInt(3,(int)iUser.getDepartmentId());
            }
            ResultSet rs = pstm.executeQuery();
            while (rs.next()){
                User user = new User();
                user = new User();
                user.setId(rs.getLong("id"));
                user.setUsername( rs.getString("username"));
                user.setSex(rs.getLong("sex"));
                user.setClassr(rs.getString("classr"));
                user.setDepartmentId(rs.getInt("department_id"));
                user.setDepartment(rs.getString("department"));
                user.setGroupId(rs.getInt("group_id"));
                user.setGroup(rs.getString("group"));
                user.setPhone(rs.getString("phone"));
                user.setAvater(rs.getString("avater"));
                user.setPermissions(rs.getInt("permissions"));
                users.add(user);
            }
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

}
